#!/usr/bin/perl

use DBI;

$SRC_DB=$ENV{'SRC_DB'};
$TGT_DB=$ENV{'TGT_DB'};

#$SRC_DB='KEBCS_140';
sub fetch_msg_fmt
{
	local($fmt_name) = @_;

#		printf "$input_ind $compound_ind \n";


	if ( $compound_ind == 1) {
		$sql_sub01 = "NNF_COMPOUND_FMT B,";
		$sql_sub02 = "
		AND B.FMT_ID = A.FMT_ID
		AND C.FMT_ID = B.CMPNT_FMT_ID
		";
		$sql_sub03 = "
ORDER BY A.FMT_ID, B.SEQ_NUM, C.FMT_ID, D.SEQ_NUM
";
		$sql_sub04 = "
F.DATA_LEN,
C.FMT_NAME,
B.REPEAT_IND,
B.REPEAT_CNT,
B.REPEAT_TERM_ID
";
	} else {
		$sql_sub01 = "";
		$sql_sub02 = "
		AND C.FMT_ID = A.FMT_ID
		";
		$sql_sub03 = "
ORDER BY A.FMT_ID, C.FMT_ID, D.SEQ_NUM
";
		$sql_sub04 = "
F.DATA_LEN,
'',
'',
'',
''
";
	}

	$sql01 = "
SELECT
A.FMT_NAME FORMAT_NAME,
D.SEQ_NUM,
E.FLD_NAME,
'',
F.CNTL_NAME,
'',
'',
$sql_sub04
FROM NNF_FMT A,
	$sql_sub01
	NNF_FMT C,
	NNF_INPUT_FMT_FLD D,
	NNF_FLD E,
	NNF_INPUT_CNTL F
WHERE A.FMT_NAME = '$fmt_name'
$sql_sub02
AND D.FMT_ID = C.FMT_ID
AND E.FLD_ID = D.FLD_ID
AND F.INPUT_CNTL_ID = D.INPUT_CNTL_ID
$sql_sub03
";
	$sql02 = "
SELECT
A.FMT_NAME,
D.SEQ_NUM,
E.FLD_NAME TGT_FLD,
G.OUTPUT_CNTL_ID,
G.CNTL_NAME OUT_CNTL,
F.FLD_NAME IN_FLD,
I.SUBSTRING_START,
I.SUBSTRING_LEN,
C.FMT_NAME,
'',
'',
''
FROM NNF_FMT A,
	$sql_sub01
	NNF_FMT C,
	NNF_OUT_FMT_FLD D,
	NNF_FLD E,
	NNF_FLD F,
	NNF_OUT_MSTR G,
 	NNF_CNTL_DSCR H,
	NNF_SUBSTRING I
WHERE A.FMT_NAME = '$fmt_name'
$sql_sub02
AND D.FMT_ID = C.FMT_ID
AND E.FLD_ID = D.FLD_ID
AND F.FLD_ID = D.IN_FLD_ID
AND G.OUTPUT_CNTL_ID = D.OUTPUT_CNTL_ID
AND H.CNTL_ID = G.CNTL_ID
AND I.CNTL_ID (+) = H.CNTL_ID
$sql_sub03
";

	if ( $input_ind == 1) {
  		$sth01 = $db->prepare($sql01);
#print "SQL: $sql01\n";
	} else {
# 2010.01.22 ######################################## 02-> 01 #
  		$sth01 = $db->prepare($sql02);
	}

	$sth01->execute();
  
	$i=1;
	while(@data = $sth01 -> fetchrow_array){
#    		print "[$i],$data[0],$data[1],$data[2]";
#    		print ",$data[3],$data[4],$data[5],$data[6]\n";

		if( $input_ind != 1 ) {
#			print "---------------------- THIS POINT($data[0], $data[2])\n";
			&is_repeat_field( $data[0], $data[2]);
#			print "---------------------- after THIS POINT($data[9])\n";
		}

		$s_data00[$i] = $data[0];
		$s_data01[$i] = $data[1];
		$s_data02[$i] = $data[2];
		$s_data03[$i] = $data[3];
		$s_data04[$i] = $data[4];
		$s_data05[$i] = $data[5];
		$s_data06[$i] = $data[6];
		$s_data07[$i] = $data[7];
		$s_data08[$i] = $data[8];
		$s_data09[$i] = $data[9];
		$s_data10[$i] = $data[10];
		$s_data11[$i] = $data[11];
		$s_data12[$i] = $data[12];
		$s_data13[$i] = $data[13];

#    		print "|i:$i $s_data06[$i]\n";

		$i += 1;

	}
	$sth01->finish;
	$fetch_count = $i;
}

sub s_FLD {
	local($a) = @_;

	$sql02 = "
SELECT 
-- A.OUTPUT_CNTL_ID, A.CNTL_NAME, 
-- B.CNTL_ID, B.CNTL_NAME,
-- C.CHILD_ID,
D.SUBSTRING_START,
D.SUBSTRING_LEN
FROM NNF_OUT_MSTR A,
NNF_CNTL_DSCR B,
NNF_COLL_ITEM C,
NNF_SUBSTRING D
WHERE A.OUTPUT_CNTL_ID = $s_data03[$a]
AND B.CNTL_ID = A.CNTL_ID
AND C.CNTL_ID = B.CNTL_ID
AND D.CNTL_ID = C.CHILD_ID
";
  	$sth02 = $db->prepare($sql02);

	$sth02->execute();
	while(@data = $sth02 -> fetchrow_array){
#   		print "[$i],$data[0],$data[1],$data[2]";
		$s_data06[$a] = $data[0]; 
		$s_data07[$a] = $data[1]; 
	}

#	printf "$s_data00[1]\n";
	$sth02->finish;

}

sub s_DFLT_SPACE {
	local($a) = @_;

	$sql02 = "
SELECT 
-- A.OUTPUT_CNTL_ID, A.CNTL_NAME,
-- B.CNTL_TYPE,
-- B.CNTL_ID, B.CNTL_NAME,
D.LITRL_LEN, D.LITRL_VAL
FROM NNF_OUT_MSTR A,
NNF_CNTL_DSCR B,
NNF_DEFAULT C,
NNF_LITRL D
WHERE A.OUTPUT_CNTL_ID = $s_data03[$a]
AND B.CNTL_ID = A.CNTL_ID
AND C.CNTL_ID = A.CNTL_ID
AND D.LITRL_ID = C.LITRL_ID
";
  	$sth02 = $db->prepare($sql02);

	$sth02->execute();
	while(@data = $sth02 -> fetchrow_array){
#    		print "[$i],$data[0],$data[1],$data[2]";
		$s_data06[$a] = $data[0]; 
		$s_data07[$a] = $data[1]; 
	}

#	printf "$s_data00[1]\n";
	$sth02->finish;

}

sub fetch_msg_typ
{
	local($fmt_name) = @_;
	$sql02 = "
SELECT 
	A.INPUT_IND,
	A.COMPOUND_IND
FROM NNF_FMT A
WHERE A.FMT_NAME = '$fmt_name'
";

  	$sth02 = $db->prepare($sql02);

	$sth02->execute();
	while(@data = $sth02 -> fetchrow_array){
#   		print "[$i],$data[0],$data[1],$data[2]";
		$input_ind = $data[0]; 
		$compound_ind = $data[1]; 
	}

#	printf "$s_data00[1]\n";
	$sth02->finish;
}

sub is_repeat_field
{
	local($fmt_name, $fld_name) = @_;
	$sql22 = "
SELECT B.repeat_ind, B.REPEAT_CNT
FROM NNF_FMT A, NNF_COMPOUND_FMT B, NNF_FMT C, NNF_OUT_FMT_FLD D, NNF_FLD E
WHERE a.fmt_name = '$fmt_name'
AND E.FLD_NAME = '$fld_name'
AND B.FMT_ID = A.FMT_ID
AND C.FMT_ID = B.CMPNT_FMT_ID
AND D.fmt_id = C.fmt_id
AND D.FLD_ID = E.FLD_ID
";

  	$sth22 = $db->prepare($sql22);

	$sth22->execute();
	while(@data22 = $sth22 -> fetchrow_array){
#   		print "[$i],$data[0],$data[1],$data[2]";
		$data[9] = $data22[0]; 
		$data[10] = $data22[1]; 
#		printf "-------------select : $data22[0]\n";
		$is_repeat_field = $data22[0]; 
	}


	$sth22->finish;
}

#&select_node;

$fmt_name = @ARGV[0];
$db = DBI->connect('DBI:ODBC:'.$SRC_DB, 'hubiio', 'hubiio');

&fetch_msg_typ($fmt_name);
#printf "########################## input_ind:$input_ind compound_ind:$compound_ind \n";

&fetch_msg_fmt($fmt_name);

#&fetch_msg_info;

for( $k = 1; $k < $fetch_count; $k++ ) {

#	if( $s_data04[$k] eq "OC.DFLT.H" ) {
#		$s_data06[$k] = 1; $s_data07[$k] = 1;
#	}

	if( ( $s_data04[$k] =~ /OC.FLD.0/ ) ||
		( $s_data04[$k] =~ /OC.FLD.COMMHEADER/ ) ||
		( $s_data04[$k] =~ /OC.FLD.NEXTHEADER/ ) ||
		( $s_data04[$k] =~ /OC.SBST.0001.DT/ ) ||
		( $s_data04[$k] =~ /OC.DFLT.TODAY/ ) 
	) {
		&s_FLD($k);
	}

	if( $s_data04[$k] =~ /OC.DFLT./ ) {
		&s_DFLT_SPACE($k);
	}

#$s_data09[$k] = '1';

	printf "$s_data00[$k],";
	printf "$s_data01[$k],";
	printf "$s_data02[$k],";
#	printf "$s_data03[$k],";
	printf "$s_data04[$k],";
	printf "$s_data05[$k],";
	printf "$s_data06[$k],";
	printf "$s_data07[$k],";
	printf "$s_data08[$k],";
	printf "$s_data09[$k],";
	printf "$s_data10[$k],";
	printf "$s_data11[$k]\n";
}

$db->disconnect;

